export const PasswordModel = {
  async findByUser(env, userId) {
    const stmt = env.DB.prepare(`
      SELECT p.id, p.url, p.username, p.note, p.created_at FROM passwords p
      WHERE p.user_id = ?
      ORDER BY p.url, p.created_at DESC
    `);
    return await stmt.bind(userId).all();
  },

  async getCountByUser(env, userId) {
    const stmt = env.DB.prepare('SELECT COUNT(*) as count FROM passwords WHERE user_id = ?');
    const result = await stmt.bind(userId).first();
    return result ? result.count : 0;
  },

  async create(env, { userId, url, username, encryptedPassword, passwordIv, note }) {
    const stmt = env.DB.prepare(`
      INSERT INTO passwords (
        user_id, url, username, 
        encrypted_password, password_iv,
        note
      ) VALUES (?, ?, ?, ?, ?, ?)
    `);

    return await stmt.bind(
      userId,      
      url,
      username,
      encryptedPassword,
      passwordIv,
      note
    ).run();
  },

  async update(env, id, data) {
    const sets = []
    const values = [] 
    
    if (data.url) {
      sets.push('url = ?')
      values.push(data.url)
    }
    
    if (data.username) {
      sets.push('username = ?')
      values.push(data.username)
    }
    
    if (data.encryptedPassword) {
      sets.push('encrypted_password = ?')
      values.push(data.encryptedPassword)
    }
    
    if (data.passwordIv) {
      sets.push('password_iv = ?')
      values.push(data.passwordIv)
    }
    
    if (data.note) {
      sets.push('note = ?')
      values.push(data.note)
    }
    
    if (sets.length === 0) return null
    
    values.push(id)
    const query = `UPDATE passwords SET ${sets.join(', ')}, updated_at = DATETIME('now') WHERE id = ?`
    
    const stmt = env.DB.prepare(query)
    return await stmt.bind(...values).run()
  },

  async delete(env, id) {
    const stmt = env.DB.prepare('DELETE FROM passwords WHERE id = ?');
    return await stmt.bind(id).run();
  },

  async findById(env, id) {
    const stmt = env.DB.prepare(`
      SELECT p.* FROM passwords p
      WHERE p.id = ?
    `);
    return await stmt.bind(id).first();
  }
} 